package com.example.demo.bean;

import com.example.demo.core.enums.ElasticTypeEnum;
import org.apache.commons.lang.NullArgumentException;
import org.springframework.util.StringUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;

public final class BuildOldHisSql {

    public static String getSql(String theme, String timestamps, int limit) throws ParseException {
        if (StringUtils.isEmpty(theme)) {
            throw new NullArgumentException("theme");
        }

        ElasticTypeEnum typeEnum = ElasticTypeEnum.getByTheme(theme);
        if (typeEnum == null) {
            throw new NullPointerException(theme + " 找不到指定ES类型");
        }
        String sql = null;
        switch (typeEnum) {
            case PATIENT:
                sql = getPatientSql(timestamps, limit);
                break;
            case MEDICAL_RECORD:
                sql = getAdmSql(timestamps, limit);
                break;
            case DIAGNOSE:
                sql = getDiagnoseSql(timestamps, limit);
                break;
            case ORDITEM:
                sql = getOrdItemSql(timestamps, limit);
                break;
            case Lisitem:
                sql = getLisitemSql(timestamps, limit);
                break;
            case OrdRis:
                sql = getOrdRisSql(timestamps, limit);
                break;
            case Operation:
                sql = getOperation(timestamps, limit);
                break;
            default:
                break;
        }

        return sql;
    }


    private static String getPatientSql(String time, int limit) throws ParseException {
        String sql = "SELECT " +
                " pa_patmas.papmi_no AS pat_regno," +
                " pa_patmas.papmi_name AS pat_name," +
                " pa_patmas.papmi_sex_dr AS pat_gender_code," +
                " pa_patmas.papmi_dob AS pat_birthday," +
                " pa_patmas.t_timestamp AS t_timestamp," +
                " pa_person.paper_id AS pat_idcard," +
                " pa_person.paper_marital_dr AS pat_marital_code, pa_person.paper_nation_dr AS pat_nation_code, pa_person.t_timestamp AS pasontimestamp, pa_person.paper_updatedate AS papatupdatedate, pa_person.paper_updatetime AS papatupdatetime " +
                " FROM db002_dsn0003_sqluser.pa_patmas pa_patmas " +
                " INNER JOIN db002_dsn0003_sqluser.pa_person pa_person" +
                " ON pa_person.paper_rowid = pa_patmas.papmi_rowid";
        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("pa_patmas.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    private static String getAdmSql(String time, int limit) throws ParseException {
        String sql = "SELECT " +
                " pa_adm.paadm_rowid AS mr_admid," +
                " pa_patmas.papmi_no AS mr_regno," +
                " pa_patmas.papmi_dob AS mr_birthday," +
                " pa_adm.paadm_hospital_dr AS mr_admhospital_code," +
                " pa_adm.paadm_depcode_dr AS mr_admdept_code," +
                " pa_adm.paadm_type AS mr_admtype_code," +
                " pa_adm.paadm_admdate AS mr_admdate," +
                " pa_adm.paadm_admtime AS mr_admtime," +
                " pa_adm.paadm_dischgdate AS mr_dischdate," +
                " pa_adm.paadm_dischgtime AS mr_dischtime," +
                " pa_adm.paadm_dispos_dr AS mr_dischdept_code," +
                " pa_adm.paadm_visitstatus AS mr_visitstatus_code," +
                " pa_adm.t_timestamp AS adm_timestamp" +
                " FROM db002_dsn0003_sqluser.pa_adm pa_adm" +
                " LEFT JOIN db002_dsn0003_sqluser.pa_patmas pa_patmas" +
                " ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr";
        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("pa_adm.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    /**
     * 获取诊断sql
     * @param time
     * @param limit
     * @return
     */
    private static String getDiagnoseSql(String time, int limit) throws ParseException {
        String sql = "SELECT \n" +
                " mr_diagnos.mrdia_rowid AS diag_id, \n" +
                " pa_adm.paadm_rowid AS diag_admid, \n" +
                " pa_patmas.papmi_no AS diag_regno, \n" +
                " mr_diagnos.mrdia_date AS diag_date, \n" +
                " mr_diagnos.mrdia_time AS diag_time, \n" +
                " mr_diagtype.typ_mrcdiagtyp AS diag_type_code, \n" +
                " mr_diagnos.mrdia_desc AS diag_name, \n" +
                " mr_diagnos.mrdia_icdcode_dr AS diag_code, \n" +
                " mr_diagnos.t_timestamp AS diag_timestamp\n" +
                "FROM db002_dsn0003_sqluser.mr_diagnos mr_diagnos\n" +
                "\tINNER JOIN db002_dsn0003_sqluser.mr_adm mr_adm ON mr_adm.mradm_rowid = mr_diagnos.mrdia_mradm_parref\n" +
                "\tINNER JOIN db002_dsn0003_sqluser.pa_adm pa_adm ON pa_adm.paadm_mainmradm_dr = mr_adm.mradm_rowid\n" +
                "\tINNER JOIN db002_dsn0003_sqluser.pa_patmas pa_patmas ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.mr_diagtype mr_diagtype \n" +
                "\tON mr_diagtype.typ_parref = mr_diagnos.mrdia_rowid ";
        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("mr_diagnos.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    /**
     * 获取his老系统医嘱的SQL， 同时会导出用药和检验的医嘱
     * @param time
     * @param limit
     * @return
     * @throws ParseException
     */
    private static String getOrdItemSql(String time, int limit) throws ParseException {
        String sql = "SELECT oe_orditem.oeori_rowid AS ord_id, " +
                " pa_adm.paadm_rowid AS ord_admno, " +
                " pa_patmas.papmi_no AS ord_regno, " +
                " oe_orditem.oeori_itmmast_dr AS ord_code, " +
                " oe_orditem.oeori_opercategory_dr AS ord_category_code\n" +
                "\t, oe_orditem.oeori_priority_dr AS ord_type_code, " +
                " oe_orditem.oeori_durat_dr AS ord_duration_code, " +
                " oe_orditem.oeori_instr_dr AS ord_usage_code, " +
                " oe_orditem.oeori_phfreq_dr AS ord_freq_code, " +
                " oe_orditem.oeori_sttdat AS ord_startdate\n" +
                "\t, oe_orditem.oeori_stttim AS ord_starttime," +
                " oe_orditem.oeori_itemstat_dr AS ord_status_code," +
                " oe_orditem.oeori_phqtyord AS ord_orderqty," +
                " oe_orditem.oeori_enddate AS ord_enddate," +
                " oe_orditem.oeori_endtime AS ord_endtime\n" +
                "\t, oe_orditem.oeori_orddept_dr AS ord_dept_code," +
                " oe_orditem.oeori_dateexecuted AS ord_dateexecuted," +
                " oe_orditem.oeori_doseqty AS ord_doseqty," +
                " oe_orditem.t_timestamp AS ord_timestamp\n" +
                " FROM db002_dsn0003_sqluser.oe_orditem oe_orditem\n" +
                "\t LEFT JOIN db002_dsn0003_sqluser.oe_order oe_order ON oe_order.oeord_rowid = oe_orditem.oeori_oeord_parref\n" +
                "\t LEFT JOIN db002_dsn0003_sqluser.pa_adm pa_adm ON pa_adm.paadm_rowid = oe_order.oeord_adm_dr\n" +
                "\t LEFT JOIN db002_dsn0003_sqluser.pa_patmas pa_patmas ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr\n" +
                " ";

        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("oe_orditem.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    /**
     * 抽取HIS老系统检查
     * @param time
     * @param limit
     * @return
     * @throws ParseException
     */
    private static String getOrdRisSql(String time, int limit) throws ParseException {
        String sql = " SELECT multimedia_info.record_no AS or_id, " +
                " pa_adm.paadm_rowid AS ord_admno," +
                " pa_patmas.papmi_no AS ord_regno," +
                " multimedia_info.report_date AS or_date," +
                " register_info.body_of_case AS or_name\n" +
                "\t, oe_orditem.oeori_itmmast_dr AS or_ordcode," +
                " multimedia_info.report_desc_txt AS or_desc," +
                " multimedia_info.report_diag_txt AS or_result," +
                " multimedia_info.ods_updatetime AS ods_updatetime," +
                " multimedia_info.t_timestamp AS ris_timestamp\n" +
                "FROM db002_dsn0006_pacs3i.view_multimedia_info multimedia_info\n" +
                "\tINNER JOIN db002_dsn0006_pacs3i.view_register_info register_info ON register_info.record_no = multimedia_info.record_no\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.oe_orditem oe_orditem ON oe_orditem.oeori_rowid = replace(register_info.his_uid, '_', '||')\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.oe_order oe_order ON oe_order.oeord_rowid = oe_orditem.oeori_oeord_parref\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.pa_adm pa_adm ON pa_adm.paadm_rowid = oe_order.oeord_adm_dr\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.pa_patmas pa_patmas ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr\n" +
                "WHERE char_length(register_info.his_uid) > 0 and  char_length(pa_adm.paadm_rowid) > 0";

        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("multimedia_info.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    private static String getLisitemSql(String time, int limit) throws ParseException {
        String sql = "SELECT \n" +
                "\tlis_inspection_result.lis_id AS lis_id, \n" +
                "\tlis_inspection_result.test_item_id AS test_item_id, \n" +
                "\tlis_inspection_result.group_id AS group_id, \n" +
                "\tlis_inspection_result.inspection_date AS lis_date,\n" +
                "\tlis_inspection_result.inspection_time AS lis_time,\n" +
                "\tlis_inspection_result.sample_number AS lis_sample_number, \n" +
                "\tlis_inspection_result.english_name AS lis_code,\n" +
                "\tlis_inspection_result.chinese_name AS lis_name, \n" +
                "\tlis_inspection_result.quantitative_result AS lis_value,\n" +
                "\tlis_inspection_result.qualitative_result AS lis_result,\n" +
                "\tlis_inspection_result.test_item_unit AS lis_unit, \n" +
                "\tlis_inspection_result.dhcchis_id AS his_id,\n" +
                "\toe_orditem.oeori_rowid AS oeori_rowid, \n" +
                "\toe_orditem.oeori_itmmast_dr AS lis_ordcode, \n" +
                "\toe_orditem.oeori_itmmast_dr || '_' || lis_inspection_result.english_name AS lis_searchcode, \n" +
                "\toe_order.oeord_date AS oeord_date, \n" +
                "\tpa_adm.paadm_rowid AS lis_admno, \n" +
                "\tpa_patmas.papmi_no AS lis_regno,\n" +
                "\tlis_inspection_result.t_timestamp AS at_timestamp\n" +
                "FROM (\n" +
                "\tSELECT a.inspection_id || a.test_item_id AS lis_id\n" +
                "\t\t, replace(a.his_id, '@', '||') AS dhcchis_id\n" +
                "\t\t, *\n" +
                "\tFROM db002_dsn0006_lis_dbo.lis_inspection_result a\n" +
                ") lis_inspection_result\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.oe_orditem oe_orditem ON oe_orditem.oeori_rowid = lis_inspection_result.dhcchis_id\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.oe_order oe_order ON oe_order.oeord_rowid = oe_orditem.oeori_oeord_parref\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.pa_adm pa_adm ON pa_adm.paadm_rowid = oe_order.oeord_adm_dr\n" +
                "\tLEFT JOIN db002_dsn0003_sqluser.pa_patmas pa_patmas ON pa_patmas.papmi_rowid = pa_adm.paadm_papmi_dr\n" +
                " where char_length(pa_patmas.papmi_no) > 0\n" +
                "\tAND char_length(pa_adm.paadm_rowid) > 0";
        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
                .append(BuildBaseSql.getWhere("lis_inspection_result.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }


    private static String getOperation(String time, int limit) throws ParseException {
        String sql = "SELECT tmrdde.fid AS admno,\n" +
                "        pa_patmas.papmi_no AS regno,\n" +
                "         tmrdde.fmrdid AS oper_id,\n" +
                "         tmrdop.fopid AS oper_code,\n" +
                "         tmrdop.fopdoc AS operator,\n" +
                "         tmrdop.fopfz1 AS opera_assistant_fir,\n" +
                "         tmrdop.fopfz2 AS opera_assistant_sec,\n" +
                "         tmrdop.fopdat AS oper_date,\n" +
                "         tmrdop.fyhtyp AS op_type,\n" +
                "         topsm.fdesc AS oper_name,\n" +
                "         thocus.fdesc AS anaes_way,\n" +
                "         tmrdop.fmzys AS anaes_doc,\n" +
                "         tcutm.fdesc AS ncision_healing_level,\n" +
                "         tmrdop.t_timestamp\n" +
                " FROM db002_dsn0006_mhis.tmrdop\n" +
                " LEFT JOIN db002_dsn0006_mhis.tmrdde\n" +
                "    ON tmrdde.fmrdid = tmrdop.fmrdid\n" +
                " LEFT JOIN db002_dsn0006_mhis.topsm\n" +
                "    ON topsm.fid = tmrdop.fopid\n" +
                " LEFT JOIN db002_dsn0006_mhis.thocus\n" +
                "    ON thocus.fid = tmrdop.fmzth\n" +
                " LEFT JOIN db002_dsn0006_mhis.tcutm\n" +
                "    ON tcutm.fid = tmrdop.fcut\n" +
                " LEFT JOIN db002_dsn0003_sqluser.pa_patmas pa_patmas\n" +
                "    ON pa_patmas.papmi_rowid = tmrdde.fid\n" +
                " WHERE tmrdde.fid is NOT NULL ";
        StringBuilder sb = new StringBuilder();
        sb.append(sql)
                .append(" ")
//                .append(BuildBaseSql.getWhere("tmrdop.t_timestamp", time, sql))
                .append(" ")
                .append(BuildBaseSql.getLimit(limit));

        return sb.toString();
    }

    public static void main(String[] args) throws ParseException {
        String timestemp = "2019-11-11";
        System.out.println("patient sql: " + getPatientSql(timestemp, 10));

        System.out.println("adm sql: " + getAdmSql(timestemp, 10));

        System.out.println("diagnose sql: " + getDiagnoseSql(timestemp, 10));

        System.out.println("ordris sql: " + getOrdRisSql(timestemp, 10));

        System.out.println("lisitem sql " + getLisitemSql(timestemp, 10));

        System.out.println("operation sql " + getOperation(timestemp, 10));
    }
}
